package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.MarketDTO;
import com.fact.utils.connection.ConnectionManager;

public class MarketService {
	
	
	public ArrayList<MarketDTO> getMarketList()
	{
		MarketDTO market=null;
		ArrayList<MarketDTO> marketList=new ArrayList<MarketDTO>();
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from MST_MARKETS order by MARKET_NAME";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				while (r.next())
				{
					market=new MarketDTO();
					market.setMarketId(r.getInt("MARKET_ID"));
					market.setMarketName(r.getString("MARKET_NAME"));
					marketList.add(market);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return marketList;
	}
	
	public boolean saveMarket(MarketDTO	market)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_MARKETS(MARKET_ID,MARKET_NAME) " +
				   " values(SQN_MARKET.nextval,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,market.getMarketName());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteMarket(int marketId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_MARKETS Where MARKET_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,marketId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editMarket(MarketDTO market)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_MARKETS  set MARKET_NAME=?  Where MARKET_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,market.getMarketId());
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public MarketDTO getMarket(int marketId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * FROM MST_MARKETS  Where MARKET_ID=?";
		PreparedStatement stmt = null;
		MarketDTO  market=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,marketId);
				r = stmt.executeQuery();
				if (r.next())
				{
					market=new MarketDTO();
					market.setMarketId(r.getInt("MARKET_ID"));
					market.setMarketName(r.getString("MARKET_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return market;
	}
	
	public MarketDTO getMarket(String marketName)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * FROM MST_MARKETS  Where lower(MARKET_NAME)=lower(?)";
		PreparedStatement stmt = null;
		MarketDTO  market=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,marketName);
				r = stmt.executeQuery();
				if (r.next())
				{
					market=new MarketDTO();
					market.setMarketId(r.getInt("MARKET_ID"));
					market.setMarketName(r.getString("MARKET_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return market;
	}

}
